package benchmarks.tpch.mysql.queries;

public class Q8MySQL extends GenericQueryMySQL {
    public Q8MySQL() {
        super.queryName = queryName;
        super.query = query;
    }
    public static final String queryName = "Q8";
//    public static final String query =
//            "select" +
//                "o_year,"+
//                "sum(case"+
//                    "when nation = 'BRAZIL' then volume"+
//                    "else 0"+
//                "end) / sum(volume) as mkt_share"+
//            "from"+
//                "("+
//                    "select"+
//                        "extract(year from o_orderdate) as o_year,"+
//                        "l_extendedprice * (1 - l_discount) as volume,"+
//                        "n2.n_name as nation"+
//                    "from"+
//                        "part,"+
//                        "supplier,"+
//                        "lineitem,"+
//                        "orders,"+
//                        "customer,"+
//                        "nation n1,"+
//                        "nation n2,"+
//                        "region"+
//                    "where"+
//                        "p_partkey = l_partkey"+
//                        "and s_suppkey = l_suppkey"+
//                        "and l_orderkey = o_orderkey"+
//                        "and o_custkey = c_custkey"+
//                        "and c_nationkey = n1.n_nationkey"+
//                        "and n1.n_regionkey = r_regionkey"+
//                        "and r_name = 'AMERICA'"+
//                        "and s_nationkey = n2.n_nationkey"+
//                        "and o_orderdate between date '1995-01-01' and date '1996-12-31'"+
//                        "and p_type = 'ECONOMY ANODIZED STEEL'"+
//                ") as all_nations"+
//            "group by"+
//                "o_year"+
//            "order by"+
//                "o_year;";
    public static final String query =
        "select\n" +
        "\to_year,\n" +
        "\tsum(case\n" +
        "\t\twhen nation = 'BRAZIL' then volume\n" +
        "\t\telse 0\n" +
        "\tend) / sum(volume) as mkt_share\n" +
        "from\n" +
        "\t(\n" +
        "\t\tselect\n" +
        "\t\t\textract(year from o_orderdate) as o_year,\n" +
        "\t\t\tl_extendedprice * (1 - l_discount) as volume,\n" +
        "\t\t\tn2.n_name as nation\n" +
        "\t\tfrom\n" +
        "\t\t\tpart,\n" +
        "\t\t\tsupplier,\n" +
        "\t\t\tlineitem,\n" +
        "\t\t\torders,\n" +
        "\t\t\tcustomer,\n" +
        "\t\t\tnation n1,\n" +
        "\t\t\tnation n2,\n" +
        "\t\t\tregion\n" +
        "\t\twhere\n" +
        "\t\t\tp_partkey = l_partkey\n" +
        "\t\t\tand s_suppkey = l_suppkey\n" +
        "\t\t\tand l_orderkey = o_orderkey\n" +
        "\t\t\tand o_custkey = c_custkey\n" +
        "\t\t\tand c_nationkey = n1.n_nationkey\n" +
        "\t\t\tand n1.n_regionkey = r_regionkey\n" +
        "\t\t\tand r_name = 'AMERICA'\n" +
        "\t\t\tand s_nationkey = n2.n_nationkey\n" +
        "\t\t\tand o_orderdate between date '1995-01-01' and date '1996-12-31'\n" +
        "\t\t\tand p_type = 'ECONOMY ANODIZED STEEL'\n" +
        "\t) as all_nations\n" +
        "group by\n" +
        "\to_year\n" +
        "order by\n" +
        "\to_year";
}
